package com.huaxia.dao.deptLevels;

import com.huaxia.pojo.deptLevels.DeptBC;
import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/08/04 9:07
 */

public interface DeptBCMapper {

    @Select("select a.branchsname,a.deptname4,a.issueMoney4,a.agentNum from(\n" +
            "select a.branchsname,a.deptname4,b.issueMoney4,a.agentNum from(\n" +
            "select sb.branchsname,a.deptcode4,a.deptname4,b.agentNum  from SD_BRANCH sb right join(\n" +
            "select t.branchcode,t.deptcode4,t.deptname4 from D_AGENT_PROVINCE_YX2 t where t.agentgrade in('BC','SBC') and t.agentstate='在职')a on sb.branchcode=a.branchcode\n" +
            "left join (\n" +
            "select  t.branchcode,t.deptcode4,t.deptname4, count(*) agentNum  from d_agent_province_yx2 t where deptname4 like '%组'\n" +
            "and t.agentstate='在职' group by t.deptcode4,t.deptname4,t.branchcode) b on a.deptcode4=b.deptcode4\n" +
            ")a,\n" +
            "(\n" +
            "select a.deptcode4,round(NVL(a.monthInSure-b.monthYt,0),0)issueMoney4 from(\n" +
            "select t.deptcode4,SUM(WRITTENSTADPREM)monthInSure from FACT_YX_PREPREM_LIST_day t where  deptname4 like '%组' and\n" +
            "TO_CHAR(ISSUE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode4\n" +
            ")a left join (select t.deptcode4,SUM(WRITTENSTADPREM)monthYt from FACT_YX_PREPREM_LIST_day t where   deptname4 like '%组' and\n" +
            "TO_CHAR(yt_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and RNFLAG='N'  group by t.deptcode4)b on a.deptcode4=b.deptcode4\n" +
            ")b where a.deptcode4=b.deptcode4 order by b.issueMoney4 DESC)a where rownum <= '30'")
    List<DeptBC> getDeptBC();
}
